﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Register.Model;
using Register.DBUtility;
using Register.Command;
using Register.IDAL;
using System.Data.SqlClient;
using System.Data;
namespace Register.DAL
{
    /// <summary> 
    /// FileName: DoctorInfoDAL.cs    
    /// CLRVersion: 4.0.30319.269    
    /// Author: DoubleZ    
    /// Corporation:     
    /// Description:     
    /// DateTime: 2012/6/29 20:00:56    
    /// </summary>
    public class DoctorInfoDAL : IDoctorInfoDAL
    {
        private string tableName = "System_doctorInfo";
        #region  添加医生
        /// <summary>
        /// 添加医生信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddDoctorInfo(Model.DoctorInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into System_doctorInfo(");
            strSql.Append("Doctor_ID,Departments_ID,Doctor_No,Doctor_Name,Doctor_Gender,Doctor_DateOfBirth,Doctor_Position,Doctor_Education,Doctor_Specialty,Doctor_Resume,Doctor_Phone,Doctor_Email,Doctor_Picture,Doctor_AddTime,Doctor_Recoder,Doctor_State)");
            strSql.Append(" values (");
            strSql.Append("@Doctor_ID,@Departments_ID,@Doctor_No,@Doctor_Name,@Doctor_Gender,@Doctor_DateOfBirth,@Doctor_Position,@Doctor_Education,@Doctor_Specialty,@Doctor_Resume,@Doctor_Phone,@Doctor_Email,@Doctor_Picture,@Doctor_AddTime,@Doctor_Recoder,@Doctor_State)");
            SqlParameter[] parameters = {
					new SqlParameter("@Doctor_ID", SqlDbType.Char,14),
					new SqlParameter("@Departments_ID", SqlDbType.Char,14),
					new SqlParameter("@Doctor_No", SqlDbType.Char,14),
					new SqlParameter("@Doctor_Name", SqlDbType.VarChar,20),
					new SqlParameter("@Doctor_Gender", SqlDbType.Char,4),
					new SqlParameter("@Doctor_DateOfBirth", SqlDbType.DateTime),
					new SqlParameter("@Doctor_Position", SqlDbType.Char,4),
					new SqlParameter("@Doctor_Education", SqlDbType.Char,4),
					new SqlParameter("@Doctor_Specialty", SqlDbType.NVarChar,100),
					new SqlParameter("@Doctor_Resume", SqlDbType.VarChar,200),
					new SqlParameter("@Doctor_Phone", SqlDbType.Char,11),
					new SqlParameter("@Doctor_Email", SqlDbType.VarChar,30),
					new SqlParameter("@Doctor_Picture", SqlDbType.VarChar,50),
					new SqlParameter("@Doctor_AddTime", SqlDbType.DateTime),
					new SqlParameter("@Doctor_Recoder", SqlDbType.VarChar,20),
					new SqlParameter("@Doctor_State", SqlDbType.Char,1)
				
                                 };
            parameters[0].Value = model.Doctor_ID;
            parameters[1].Value = model.Departments_ID;
            parameters[2].Value = model.Doctor_No;
            parameters[3].Value = model.Doctor_Name;
            parameters[4].Value = model.Doctor_Gender;
            parameters[5].Value = model.Doctor_DateOfBirth;
            parameters[6].Value = model.Doctor_Position;
            parameters[7].Value = model.Doctor_Education;
            parameters[8].Value = model.Doctor_Resume;
            parameters[9].Value = model.Doctor_Specialty;
            parameters[10].Value = model.Doctor_Phone;
            parameters[11].Value = model.Doctor_Email;
            parameters[12].Value = model.Doctor_Picture;
            parameters[13].Value = model.Doctor_AddTime;
            parameters[14].Value = model.Doctor_Recoder;
            parameters[15].Value = model.Doctor_State;

            return (int)DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion
        #region 更新医生
        public int UpdateDoctorInfo(DoctorInfo doctorInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_doctorInfo set ");
            strSql.Append("Departments_ID=@Departments_ID,");
            strSql.Append("Doctor_Name=@Doctor_Name,");
            strSql.Append("Doctor_Gender=@Doctor_Gender, ");
            strSql.Append("Doctor_DateOfBirth=@Doctor_DateOfBirth, ");
            strSql.Append("Doctor_Position=@Doctor_Position, ");
            strSql.Append("Doctor_Education=@Doctor_Education, ");
            strSql.Append("Doctor_Specialty=@Doctor_Specialty, ");
            strSql.Append("Doctor_Resume=@Doctor_Resume, ");
            strSql.Append("Doctor_Phone=@Doctor_Phone, ");
            strSql.Append("Doctor_Email=@Doctor_Email, ");
            strSql.Append("Doctor_Picture=@Doctor_Picture, ");
            strSql.Append("Doctor_LastAmendPerson=@Doctor_LastAmendPerson,");
            strSql.Append("Doctor_LastAmendTime=@Doctor_LastAmendTime");
            strSql.Append(" where Doctor_ID=@Doctor_ID");
            SqlParameter[] parameters = {	
					new SqlParameter("@Departments_ID", SqlDbType.Char,14),
					new SqlParameter("@Doctor_Name", SqlDbType.VarChar,20),
					new SqlParameter("@Doctor_Gender", SqlDbType.Char,4),
					new SqlParameter("@Doctor_DateOfBirth", SqlDbType.DateTime),
					new SqlParameter("@Doctor_Position", SqlDbType.Char,4),
					new SqlParameter("@Doctor_Education", SqlDbType.Char,4),
					new SqlParameter("@Doctor_Specialty", SqlDbType.NVarChar,100),
					new SqlParameter("@Doctor_Resume", SqlDbType.VarChar,200),
					new SqlParameter("@Doctor_Phone", SqlDbType.Char,11),
					new SqlParameter("@Doctor_Email", SqlDbType.VarChar,30),
					new SqlParameter("@Doctor_Picture", SqlDbType.VarChar,50),
                    new SqlParameter("@Doctor_LastAmendPerson", SqlDbType.VarChar,20),
					new SqlParameter("@Doctor_LastAmendTime", SqlDbType.DateTime),
                    new SqlParameter("@Doctor_ID", SqlDbType.Char,14),
                                       };

            parameters[0].Value = doctorInfo.Departments_ID;

            parameters[1].Value = doctorInfo.Doctor_Name;
            parameters[2].Value = doctorInfo.Doctor_Gender;
            parameters[3].Value = doctorInfo.Doctor_DateOfBirth;
            parameters[4].Value = doctorInfo.Doctor_Position;
            parameters[5].Value = doctorInfo.Doctor_Education;
            parameters[6].Value = doctorInfo.Doctor_Specialty;
            parameters[7].Value = doctorInfo.Doctor_Resume;
            parameters[8].Value = doctorInfo.Doctor_Phone;
            parameters[9].Value = doctorInfo.Doctor_Email;
            parameters[10].Value = doctorInfo.Doctor_Picture;
            parameters[11].Value = doctorInfo.Doctor_LastAmendPerson;
            parameters[12].Value = doctorInfo.Doctor_LastAmendTime;
            parameters[13].Value = doctorInfo.Doctor_ID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion
        /// <summary>
        /// 获取科室信息
        /// </summary>
        /// <returns></returns>
        #region 获取科室信息
        public List<DepartmentsInfo> GetDepartmentsList()
        {
            String sql = " select Departments_ID,Departments_Name  from System_departmentsInfo where Departments_State=1";
            List<DepartmentsInfo> list = new List<DepartmentsInfo>();
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql);
            list = ChangeReaderToDepartmentsList(reader);
            reader.Close();
            return list;
        }
        private List<DepartmentsInfo> ChangeReaderToDepartmentsList(SqlDataReader reader)
        {
            List<DepartmentsInfo> list = new List<DepartmentsInfo>();
            while (reader.Read())
            {
                DepartmentsInfo paramter = new DepartmentsInfo();
                paramter.Departments_ID = reader.GetString(0);
                paramter.Departments_Name = reader.GetString(1);
                list.Add(paramter);
            }
            return list;
        }


        #endregion
        #region 获取医生信息
        public Model.DoctorInfo GetDoctorInfo(string doctorID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Doctor_ID,Departments_ID,Doctor_No,Doctor_Name,Doctor_Gender,Doctor_DateOfBirth,Doctor_Position,Doctor_Education,Doctor_Specialty,Doctor_Resume,Doctor_Phone,Doctor_Email,Doctor_Picture,Doctor_AddTime,Doctor_Recoder,Doctor_State from System_doctorInfo");
            strSql.Append(" where Doctor_ID=@Doctor_ID and Doctor_State=1");
            SqlParameter[] parameters = {
					new SqlParameter("@Doctor_ID", SqlDbType.Char,14)};
            parameters[0].Value = doctorID;
            return DataSetToList(DbHelperSQL.Query(strSql.ToString(), parameters)).First();
        }

        public List<Model.DoctorInfo> GetPagedDoctorInfo(int size, int pageIndex, string where)
        {
            return DataSetToList(DbHelperSQL.getPagedData(" * ", "Doctor_AddTime", where, tableName, OrderBy.DESC, pageIndex, size));
        }

        public int GetDoctorInfoCount(string where)
        {
            int count = 0;
            StringBuilder str = new StringBuilder();
            str.Append(" select count(*) from ");
            str.Append(tableName);
            if (where != null)
            {
                str.Append(" where ");
                str.Append(where);
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            else
            {
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            return count;
        }
        private List<Model.DoctorInfo> DataSetToList(DataSet ds)
        {
            List<Model.DoctorInfo> list = new List<Model.DoctorInfo>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DoctorInfo doctor = new DoctorInfo();
                doctor.Doctor_ID = (string)dr["Doctor_ID"];
                doctor.Departments_ID = (string)dr["Departments_ID"];
                doctor.Doctor_No = (string)dr["Doctor_No"];
                doctor.Doctor_Name = (string)dr["Doctor_Name"];
                doctor.Doctor_Gender = (string)dr["Doctor_Gender"];
                doctor.Doctor_DateOfBirth = (DateTime)dr["Doctor_DateOfBirth"];
                doctor.Doctor_Position = (string)dr["Doctor_Position"];
                doctor.Doctor_Education = (string)dr["Doctor_Education"];
                doctor.Doctor_Specialty = (string)dr["Doctor_Specialty"];
                doctor.Doctor_Resume = (string)dr["Doctor_Resume"];
                doctor.Doctor_Phone = (string)dr["Doctor_Phone"];
                doctor.Doctor_Email = (string)dr["Doctor_Email"];
                doctor.Doctor_Picture = (string)dr["Doctor_Picture"];
                doctor.Doctor_AddTime = (DateTime)dr["Doctor_AddTime"];
                doctor.Doctor_Recoder = (string)dr["Doctor_Recoder"];
                doctor.Doctor_State = (string)dr["Doctor_State"];

                list.Add(doctor);
            }
            return list;
        }
        #endregion
        #region 删除医生
        public int DeleteDoctorInfo(String doctorID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from System_doctorInfo ");
            strSql.Append(" where Doctor_ID=@Doctor_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Doctor_ID", SqlDbType.Char,14)};
            parameters[0].Value = doctorID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion
        #region 根据科室ID获取科室名称
        public string GetDepartmentsNameById(string departmentsID)
        {
            string strSql = "select Departments_Name  from System_departmentsInfo  where  Departments_ID='" + departmentsID + "'";
            DataSet ds = DbHelperSQL.Query(strSql);
            string departmentsName = "无";
            if (ds != null)
            {
                departmentsName = ds.Tables[0].Rows[0]["Departments_Name"].ToString();

            }
            return departmentsName;
        }

        #endregion
        /// <summary>
        /// 根据性别代号获取性别名称
        /// </summary>
        /// <param name="genderID"></param>
        /// <returns></returns>
        /*public string GetGenderNameById(string doctorID) {
             string strSql = "select  Doctor_Gender  from System_doctorInfo where Doctor_ID='" + doctorID+ "'";
             DataSet ds = DbHelperSQL.Query(strSql);
             StringBuilder genderName =new StringBuilder();
             if (ds != null)
             {  
                  StringBuilder genderCode=new StringBuilder(ds.Tables[0].Rows[0]["Doctor_Gender"].ToString());
                 if (genderCode.Equals("nan"))
                 {
                     genderName.Append("男");
                 }
                 else if (genderCode.Equals("nv"))
                 { 
                 genderName.Append("女");
                 }
             }
             return genderName.ToString();
          }*/
        #region 判断医生是否存在用户
        public bool Exists(string doctorID)
        {
            string strSql = "select  DoctorUser_ID from System_doctorInfo where Doctor_ID='" + doctorID + "'";
            DataSet ds = DbHelperSQL.Query(strSql);
           
            if (ds != null)
            {    //存在记录
                if (ds.Tables[0].Rows[0]["DoctorUser_ID"].ToString() != null && !String.IsNullOrEmpty(ds.Tables[0].Rows[0]["DoctorUser_ID"].ToString()))
                {
                    return true;
                }

                else { return false; }

            }
            else
            { return false; }
        }
        #endregion
    }
}

